#!/usr/bin/ksh
# whatindex_detail ()
#------------------------------------------------------------------------------#
#    Module Name:  whatindex_detail                                            #
#                                                                              #
#    Purpose:      Report on the Owner-Indexes.                                #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    03-May-2011   M.Barone      Module design/creation                        #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

	clear
	echo
	echo  " #--------------------------------------------------------#"
	echo  " # whatindex_detail:  Oracle Apps Database Owner-Indexes  #"
	echo  " #--------------------------------------------------------#"
	echo

	read SCHOWN?" Please Enter the [Schema-Owner] Example: ALLY_FCFCORE: "

	clear
	ORANAM=${ORASID}

#------------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                                  #
#------------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

	CONNECT / as SYSDBA

	clear	BREAKS
	clear	BUFFER
	clear	COLUMNS
	clear	COMPUTES
	clear	SCREEN
	clear	SQL
	clear	TIMING

	set	feedback	off
	set	flush		on
	set	heading		on
	set	pause		off
	set	space		1
	set	termout		on
	set	verify		on
	set	linesize	80
	set	pagesize	999

        SPOOL /tmp/whatindex_detail_$$.txt

	COLUMN	townr		FORMAT A12	HEADING 'Table Owner'
	COLUMN	tname		FORMAT A18	HEADING 'Table Name'
	COLUMN	iownr		FORMAT A12	HEADING 'Index Owner'
	COLUMN	iname		FORMAT A18	HEADING 'Index Name'
	COLUMN	cname		FORMAT A16	HEADING 'Index Column'

	BREAK	ON townr	skip 1 		ON tname	skip 0 -
		ON iownr	skip 0		ON iname	skip 2

        TTITLE  CENTER  'Oracle Indexes Used by SchemaName'		-
		RIGHT   'Page:'         FORMAT 999 SQL.pno	skip 1	-
                CENTER  'whatindex_detail -- Sorted by Schema Name'	skip 2

        SELECT  table_owner	townr,
		table_name	tname,
		index_owner	iownr,
		index_name	iname,
		column_name	cname
	 FROM	dba_ind_columns
	 WHERE  table_owner like '%${SCHOWN}%'
	ORDER	BY 1,2,3,4,5;

EXIT;

END_FILE
print
print "Please see /tmp/whatindex_detail_$$.txt"
print
}
